/**
 * @Copyright:Copyright (c) 1992 - 2016
 * @Company: TengTong
 */

package com.tengtong.duang.db.dbutil;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Types;
import java.util.Arrays;
import java.util.List;
import java.util.Objects;

import com.tengtong.duang.commons.base.DataMap;
import com.tengtong.duang.db.base.QueryHandler;
import com.tengtong.duang.db.handler.BeanListHandler;
import com.tengtong.duang.db.handler.DataMapHandler;

/**
 * 数据库操作
 * 
 * @author Jai
 *
 */
public class DBUtils {

	/**
	 * 执行查询，并返回列表
	 * 
	 * @param callback
	 * @param connection
	 * @param sql
	 * @param args
	 * @throws Exception
	 */
	public final static <T> List<T> queryList(Class<T> clazz, Connection connection, String sql, Object... params) throws SQLException{
		return query( new BeanListHandler<>(clazz), connection, sql, params);
	}

	/**
	 * 执行查询，并返回 DataMap，注意这只有一行数据
	 * 
	 * @param callback
	 * @param connection
	 * @param sql
	 * @param args
	 * @throws Exception
	 */
	public final static DataMap<String, Object> queryDataMap(Connection connection, String sql, Object... params) throws SQLException{
		return query( new DataMapHandler(), connection, sql, params);
	}
	
	/**
	 * 执行查询
	 * 
	 * @param callback
	 * @param connection
	 * @param sql
	 * @param args
	 * @throws Exception
	 */
	public final static <T> T query(QueryHandler<T> callback, Connection connection, String sql, Object... params) throws SQLException{
		Objects.requireNonNull(connection, "数据库连接不能为 null");
		
        PreparedStatement pst = null;
        ResultSet rs = null;
		try{
			pst = prepareStatement(connection, sql);
			fillStatement(pst, params);
			
			rs = pst.executeQuery();
			
			if(callback != null){
				return callback.processRow(rs);
			}
			
		}catch(SQLException e){
			rethrow(e, sql, params);
		}finally{
			try{
				close(rs);
			}finally{
				close(pst);
			}
		}
		return null;
	}

	/**
	 * 执行不需要返回 生成键值的 Insert , Update, Delete 语句
	 * 
	 * @param connection
	 * @param sql
	 * @param params
	 * @return
	 * @throws SQLException
	 */
	public final static long update(Connection connection, String sql, Object...params) throws SQLException{
		return update(connection, false, sql, params);
	}

	/**
	 * 执行不需要返回 生成键值，也没有参数的 Update, Delete 语句
	 * 
	 * @param connection
	 * @param sql
	 * @param params
	 * @return
	 * @throws SQLException
	 */
	public final static long update(Connection connection, String sql) throws SQLException{
		return update(connection, false, sql, (Object[])null);
	}
	
	/**
	 * Insert, Update, Detele 语句
	 * 
	 * @param connection  连接
	 * @param autoGeneratedKeys 是否创建与返回 id 值
	 * @param sql
	 * @param params
	 * 
	 */
	public final static long update(Connection connection, boolean autoGeneratedKeys, String sql, Object...params) throws SQLException{
        PreparedStatement stmt = null;
        int rows = 0;

        try {
            stmt = prepareStatement(connection, sql, autoGeneratedKeys);
            fillStatement(stmt, params);
            rows = stmt.executeUpdate();
            
            if(rows == 1 && autoGeneratedKeys){
            	ResultSet rs = stmt.getGeneratedKeys();
            	if(rs.next()){
            		 return Long.parseLong(rs.getString(1));
            	}
            	close(rs);
            }
            
        } catch (SQLException e) {
            rethrow(e, sql, params);
        } finally {
            close(stmt);
        }

        return rows;
		
	}
	
	/**
	 * 填充 SQL 中的参数
	 * 
	 * @param pst
	 * @param args
	 */
	private static void fillStatement(PreparedStatement pst, Object... params) throws SQLException{
		if(params == null){
			return ;
		}
		
		for(int i=0; i <params.length; i++){
			if(params[i] != null){
            	final String targetType = params[i].getClass().getName();
            	if("java.util.Date".equals(targetType)){
            		pst.setTimestamp(i+1, new java.sql.Timestamp(((java.util.Date)params[i]).getTime()));
            	}else if(params[i].getClass().isEnum()){
            		pst.setString(i + 1, params[i].toString());
            	}else {
            		pst.setObject(i + 1, params[i]);
            	}				
			}else{
                int sqlType = Types.VARCHAR;
                pst.setNull(i + 1, sqlType);
			}
		}
	}

	/**
	 * @param rs
	 * @throws SQLException 
	 */
	private static void close(ResultSet rs) throws SQLException {
		if(rs != null){
			rs.close();
		}
	}
	
	/**
	 * @param pst
	 * @throws SQLException 
	 */
	private static void close(PreparedStatement pst) throws SQLException {
		if(pst != null){
			pst.close();
		}
	}
	
	/**
	 * @param connection
	 * @throws SQLException 
	 */
	private static void close(Connection connection) throws SQLException {
		if(connection != null){
			connection.close();
		}
	}

	/**
	 * 创建 PreparedStatement
	 * 
	 * @param conn
	 * @param sql
	 * @return
	 * @throws SQLException
	 */
	private static final PreparedStatement prepareStatement(Connection conn, String sql) throws SQLException{
		return conn.prepareStatement(sql);
	}

	/**
	 * 创建 PreparedStatement
	 * 
	 * @param conn
	 * @param sql
	 * @return
	 * @throws SQLException
	 */
	private static final PreparedStatement prepareStatement(Connection conn, String sql, boolean autoGeneratedKeys) throws SQLException{
		return autoGeneratedKeys ? conn.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS) : conn.prepareStatement(sql);
	}
	
	/**
	 * 更好地显示出错信息
	 * 
	 * @param cause
	 * @param sql
	 * @param params
	 * @throws SQLException
	 */
    private static final void rethrow(SQLException cause, String sql, Object... params)
    throws SQLException {

        String causeMessage = cause.getMessage();
        if (causeMessage == null) {
            causeMessage = "";
        }
        StringBuffer msg = new StringBuffer(causeMessage);

        msg.append(" Query: ");
        msg.append(sql);
        msg.append(" Parameters: ");

        if (params == null) {
            msg.append("[]");
        } else {
            msg.append(Arrays.deepToString(params));
        }

        SQLException e = new SQLException(msg.toString(), cause.getSQLState(),
                cause.getErrorCode());
        e.setNextException(cause);

        throw e;
    }
	
}
